/***********************************************************************************************************************************
 * Name:	Trace_Usage.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Create a server-side trace to capture usage information.
 */
USE master;
GO
DECLARE @TraceFile		NVARCHAR(245);	SET @TraceFile	= DBAdmin.GetInstancePath(N'WorkingDirectory') + @@SERVERNAME + '_DBUsage';
DECLARE @StopTime		DATETIME;		SET @StopTime	= DATEADD(dd,31,GETDATE());	-- 31 Days
DECLARE @TraceID		INT; -- Trace ID created by sp_trace_create
DECLARE @ReturnValue	INT;

-- variable declarations required for data type
DECLARE @MaxFileSize	BIGINT;			SET @MaxFileSize= 128;
DECLARE @On				BIT;			SET @On			= 1;
PRINT @TraceFile


-- create the trace ================================================================================================================
EXECUTE @ReturnValue= sp_trace_create @TraceID = @TraceID OUTPUT
	, @options		= 2				-- TRACE_FILE_ROLLOVER
	, @tracefile	= @TraceFile
	, @maxfilesize	= @MaxFileSize
	, @stoptime		= @StopTime
	, @filecount	= 88;
/*
 *	Return	Description
 *	0		No error.
 *	1		Unknown error.
 *	10		Invalid options. Returned when options specified are incompatible.
 *	12		File not created.
 *	13		Out of memory. Returned when there is not enough memory to perform the specified action.
 *	14		Invalid stop time. Returned when the stop time specified has already happened.
 *	15		Invalid parameters. Returned when the user supplied incompatible parameters.
 */
SELECT @TraceID AS [Trace ID], @ReturnValue AS [Return Value];


-- set the events ==================================================================================================================

--RPC:Starting (11) 
EXECUTE sp_trace_setevent @TraceID, 11,  8, @On;	-- HostName
EXECUTE sp_trace_setevent @TraceID, 11, 10, @On;	-- ApplicationName
EXECUTE sp_trace_setevent @TraceID, 11, 11, @On;	-- LoginName 
EXECUTE sp_trace_setevent @TraceID, 11, 35, @On;	-- DatabaseName

--SQL:BatchStarting (13) 
EXECUTE sp_trace_setevent @TraceID, 13,  8, @On;	-- HostName
EXECUTE sp_trace_setevent @TraceID, 13, 10, @On;	-- ApplicationName
EXECUTE sp_trace_setevent @TraceID, 13, 11, @On;	-- LoginName 
EXECUTE sp_trace_setevent @TraceID, 13, 35, @On;	-- DatabaseName


-- start the trace =================================================================================================================
EXECUTE sp_trace_setstatus
	@TraceID= @TraceID,
	@status	= 1;
--0	Stops the specified trace.
--1	Starts the specified trace.
--2	Closes the specified trace and deletes its definition from the server.

-- check trace status
SELECT * FROM dbo.fn_trace_getinfo(default);

-- Property:
--	1 = Trace Options. For more information, see @options in sp_trace_create (Transact-SQL). 
--	2 = File Name 
--	3 = Max Size 
--	4 = Stop Time 
--	5 = Current Status: 0 = stopped; 1 = running.


-- stop the trace ==================================================================================================================
/*
 EXECUTE sp_trace_setstatus
	@TraceID= 2,
	@Status	= 0;
 EXECUTE sp_trace_setstatus
	@TraceID= 2,
	@Status	= 2;
 */
-- 0 Stops the specified trace.
-- 1 Starts the specified trace.
-- 2 Closes the specified trace and deletes its definition from the server.
GO